/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dao;

import java.sql.ResultSet;
import java.sql.SQLException;

import com.mysql.jdbc.CallableStatement;
import com.mysql.jdbc.Connection;
import java.util.ArrayList;
import pojo.SachPOJO;

/**
 *
 * @author HP
 */
public class SachDAO {

    /**
     * Phuong Thuc Them Sach
     * @param sdto
     * @return
     */
    public boolean themSach(SachPOJO s) {
        boolean kq = false;
        String sql = String.format("INSERT INTO Sach(MaSach,TenSach,GiaBan,SoLuongTon,MaNhaXuatBan,MaTheLoai)"
                + " VALUES('%s','%s',%f,%d,'%s',%d)",
                s.getMaSach(), s.getTenSach(), s.getGiaBan(), s.getSoLuongTon(), s.getMaNhaXuatBan(), s.getMaTheLoai());
        MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
        helper.open();
        int n = helper.executeUpdate(sql);
        if (n == 1) {
            kq = true;//Them sach thanh cong
        }

        helper.close();
        return kq;
    }

    /**
     * Phuong Thuc Xoa Sach
     * @return
     */
    public boolean xoaSach(String maSach) {

        boolean kq = false;
        try {
            String sql = "delete from sach where MaSach='" + maSach + "'";
            // String sql = "DELETE FROM sach WHERE MaSach ='"+  maSach+"'";
            MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
            helper.open();
            int n = helper.executeUpdate(sql);
            if (n >= 1) {
                kq = true;//xoa thanh cong
            }
            helper.close();
            return kq;
        } catch (Exception ex) {
            System.out.println(ex.getMessage());
            return kq;
        }
    }

    /**
     * Phuong Thuc Cap Nhat Sach
     */
    public boolean capNhatSach(SachPOJO s) {
        boolean kq = false;

        String sql = String.format(
                "UPDATE Sach SET TenSach ='%s',GiaBan=%f,SoLuongTon=%d,MaNhaXuatBan='%s',MaTheLoai=%d"
                + " WHERE MaSach='%s' ",
                s.getTenSach(), s.getGiaBan(), s.getSoLuongTon(), s.getMaNhaXuatBan(), s.getMaTheLoai(), s.getMaSach());
        MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
        helper.open();
        int n = helper.executeUpdate(sql);
        if (n == 1) {
            kq = true;//Cap Nhat sach thanh cong
        }
        helper.close();
        return kq;
    }

    /**
     * Phuong Thuc Lay Ten Sach Theo Ma Sach
     * @param masach
     * @return
     */
    /**
     * Phuong Thuc Xem Danh sach cac Sach
     * @return
     */
    public ArrayList<SachPOJO> layDanhSachCacMaSach() {
        ArrayList<SachPOJO> ds = new ArrayList<SachPOJO>();
        try {
            String sql = "SELECT * FROM Sach";
            MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
            helper.open();
            ResultSet rs = helper.executeQuery(sql);
            while (rs.next()) {
                SachPOJO s = new SachPOJO();
                s.setMaSach(rs.getNString("MaSach"));
                s.setTenSach(rs.getNString("TenSach"));
                s.setGiaBan(rs.getFloat("GiaBan"));
                s.setSoLuongTon(rs.getInt("SoLuongTon"));
                s.setMaNhaXuatBan(rs.getNString("MaNhaXuatBan"));
                s.setMaTheLoai(rs.getInt("MaTheLoai"));
                ds.add(s);
            }
            helper.close();
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
        return ds;
    }

    /**
     * Phuong Thuc Lay Thong Tin Cua 1 Cuan Sach
     * @param maSach
     * @return
     */
    public SachPOJO layThongTinSach(String maSach) {
        SachPOJO sach = null;
        String sql = String.format("SELECT * FROM Sach WHERE MaSach='%s'", maSach);
        try {
            MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
            helper.open();
            ResultSet rs = helper.executeQuery(sql);
            if (rs.next()) {
                sach = new SachPOJO();
                sach.setMaSach(rs.getString("MaSach"));
                sach.setTenSach(rs.getString("TenSach"));
                sach.setGiaBan(rs.getFloat("GiaBan"));
                sach.setSoLuongTon(rs.getInt("SoLuongTon"));
                sach.setMaNhaXuatBan(rs.getString("MaNhaXuatBan"));
                sach.setMaTheLoai(rs.getInt("MaTheLoai"));
            }
            helper.close();
        } catch (SQLException ex) {
            sach.setMaSach("");
            System.out.println(ex.getMessage());
        }
        return sach;
    }
    //Ham Lay danh sach theo the loai

    public ArrayList<SachPOJO> layDSSach_TheoTheLoai(int maTheLoai) {

        ArrayList<SachPOJO> dssach = new ArrayList<SachPOJO>();
        try {
            MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
            helper.open();
            Connection cn = (Connection) helper.getConnection();
            CallableStatement statement = (CallableStatement) cn.prepareCall("{call sp_LayDSSach_TheoTheLoai(?)}");
            statement.setInt(1, maTheLoai);

            statement.execute();
            ResultSet rs = statement.getResultSet();
            while (rs.next()) {
                SachPOJO sdto = new SachPOJO();
                sdto.setMaSach(rs.getNString("MaSach"));
                sdto.setTenSach(rs.getNString("TenSach"));
                sdto.setGiaBan(rs.getFloat("GiaBan"));
                sdto.setSoLuongTon(rs.getInt("SoLuongTon"));
                sdto.setMaNhaXuatBan(rs.getNString("MaNhaXuatBan"));
                sdto.setMaTheLoai(rs.getInt("MaTheLoai"));
                dssach.add(sdto);

            }
            helper.close();
            return dssach;
        } catch (Exception ex) {
            System.out.print(ex.getMessage());
            return dssach;
        }
    }
    //sp_LaySachTheoMaSach
    //Ham Tim Kiem Sach Theo Ten sach

    public ArrayList<SachPOJO> timKiemSach_TheoGiaTu_GiaDen(float _giatu, float _giaden) {

        ArrayList<SachPOJO> ds = new ArrayList<SachPOJO>();
        try {
            MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
            helper.open();
            Connection cn = (Connection) helper.getConnection();
            CallableStatement statement = (CallableStatement) cn.prepareCall("{call sp_LaySachTheoGiaBan_Tu_Den(?,?)}");
            statement.setFloat(1, _giatu);
            statement.setFloat(2, _giaden);
            statement.execute();
            ResultSet rs = statement.getResultSet();
            while (rs.next()) {
                SachPOJO sdto = new SachPOJO();
                sdto.setMaSach(rs.getNString("MaSach"));
                sdto.setTenSach(rs.getNString("TenSach"));
                sdto.setGiaBan(rs.getFloat("GiaBan"));
                sdto.setSoLuongTon(rs.getInt("SoLuongTon"));
                sdto.setMaNhaXuatBan(rs.getNString("MaNhaXuatBan"));
                sdto.setMaTheLoai(rs.getInt("MaTheLoai"));
                ds.add(sdto);
            }
            helper.close();
        } catch (Exception ex) {
        }
        return ds;
    }

    public ArrayList<SachPOJO> timKiemSach_TheoTenSach(String _tenSach) {

        ArrayList<SachPOJO> ds = new ArrayList<SachPOJO>();
        try {
            MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
            helper.open();
            Connection cn = (Connection) helper.getConnection();
            CallableStatement statement = (CallableStatement) cn.prepareCall("{call sp_LaySachTheoTenSach(?)}");
            statement.setString(1, _tenSach);
            statement.execute();
            ResultSet rs = statement.getResultSet();
            while (rs.next()) {
                SachPOJO sdto = new SachPOJO();
                sdto.setMaSach(rs.getNString("MaSach"));
                sdto.setTenSach(rs.getNString("TenSach"));
                sdto.setGiaBan(rs.getFloat("GiaBan"));
                sdto.setSoLuongTon(rs.getInt("SoLuongTon"));
                sdto.setMaNhaXuatBan(rs.getNString("MaNhaXuatBan"));
                sdto.setMaTheLoai(rs.getInt("MaTheLoai"));
                ds.add(sdto);
            }
            helper.close();
        } catch (Exception ex) {
        }
        return ds;
    }

    public ArrayList<SachPOJO> LaySach_Theo_MaTheLoai_MaNhaXuatBan(int _matheloai, String _manhaxuatban) {

        ArrayList<SachPOJO> ds = new ArrayList<SachPOJO>();
        try {
            MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
            helper.open();
            Connection cn = (Connection) helper.getConnection();
            CallableStatement statement = (CallableStatement) cn.prepareCall("{call sp_LaySachTheo_MaTheLoai_MaNhaXuatBan(?,?)}");
            statement.setInt(1, _matheloai);
            statement.setString(2, _manhaxuatban);
            statement.execute();
            ResultSet rs = statement.getResultSet();
            while (rs.next()) {
                SachPOJO sdto = new SachPOJO();
                sdto.setMaSach(rs.getNString("MaSach"));
                sdto.setTenSach(rs.getNString("TenSach"));
                sdto.setGiaBan(rs.getFloat("GiaBan"));
                sdto.setSoLuongTon(rs.getInt("SoLuongTon"));
                sdto.setMaNhaXuatBan(rs.getNString("MaNhaXuatBan"));
                sdto.setMaTheLoai(rs.getInt("MaTheLoai"));
                ds.add(sdto);
            }
            helper.close();
        } catch (Exception ex) {
        }
        return ds;
    }

    // Ham lay danh sach theo ma sach
    public SachPOJO layDSSach_TheoMaSach(String maSach) {
        SachPOJO sdto = new SachPOJO();
        try {
            MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
            helper.open();
            Connection cn = (Connection) helper.getConnection();
            CallableStatement statement = (CallableStatement) cn.prepareCall("{call sp_LaySachTheoMaSach(?)}");
            statement.setString(1, maSach);

            statement.execute();
            ResultSet rs = statement.getResultSet();
            while (rs.next()) {

                sdto.setMaSach(rs.getNString("MaSach"));
                sdto.setTenSach(rs.getNString("TenSach"));
                sdto.setGiaBan(rs.getFloat("GiaBan"));
                sdto.setSoLuongTon(rs.getInt("SoLuongTon"));
                sdto.setMaNhaXuatBan(rs.getNString("MaNhaXuatBan"));
                sdto.setMaTheLoai(rs.getInt("MaTheLoai"));

            }
            helper.close();
            return sdto;
        } catch (Exception ex) {
            System.out.print(ex.getMessage());
            return sdto;
        }
    }
}
